﻿# -*- coding=utf-8 -*-

import urllib2
import re
import gzip
import StringIO
import csv
import xlrd
import xlwt

def getStrvalue(tar,strname,spstr="&nbsp;"):
    if tar.find(strname)>=0:
        ress=tar.split(strname)
        print "可以切割"+strname+"   切割后长度："+str(len(ress))
        if len(ress)>1:
            ress = ress[1].split(spstr)
            ress = ress[1].split("<")
            return ress[0]
        else:
            print "切割出错，字符串没有："+strname
    return ""

fname = "churang.xls"
print "打开文档，写入数据"
bk = xlrd.open_workbook(fname)

shxrange = range(bk.nsheets)
sheetname = "Sheet1"

try:
    sh = bk.sheet_by_name(sheetname)
except:
    print "no sheet in %s named Sheet1" % fname
print "获取数据"

#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)

i=0
dikuaibianma = "" #地块编码
dikuaiweizhi = "" #地块位置
tudiyongtu = "" #土地用途
area=""#土地面积（公顷）
selldate=""#出让年限
sellPrice=""#成交价
unit =""#受让单位src
des = ""#备注
ptime=""#发布时间
#获取所有网址

urldic=[]
while i<nrows:
    rows = sh.row_values(i)
    urlstr=rows[0]
    urldic.append(urlstr)
    i+=1

#删除重复的
print "删除重复前:"+str(len(urldic))
uselist =  list(set(urldic))
print "删除重复后:"+str(len(uselist))
nrows = len(uselist)
allDic=[]

#17页
i=0
while i<nrows :
    urlstr=uselist[i]

    print "**************************************************************"
    print str(i)+ u" 打开网页:"+urlstr
    i+=1

    response = urllib2.urlopen(urlstr)

    strl = response.read()
    response.close()
    pstrl = unicode(strl,"gb2312",'ignore').encode("utf8")
    strl = pstrl
    strl=strl.replace("\r\n","")
    strl=strl.replace(" ","")
    p=re.compile('\s+')
    strl=re.sub(p,'',strl)
    s="====================="
    lencount =2
    canread = 0
    if strl.find("一、")>=0 and strl.find("二、"):
        canread =1

    if strl.find("工业用地")>=0 or strl.find("商服用地")>=0 or strl.find("公共设施用地")>=0:
        canread=0
    if canread>0:
        results = strl.split("一、")
        print "len1:"+str(len(results))
        results = results[1].split("二、")
        print "len2:"+str(len(results))

        print "================================="
        print strl
        #获取发布时间
        sltimes = strl.split("发布时间：")
        ptime=""
        if len(sltimes)>1:
            nstr = sltimes[1]
            sltimes = nstr.split("<")
            ptime=sltimes[0]
        print "发布时间："+ptime
        print "================================="
        print results[0]
        strl=results[0]
        objDic=[]
        objDic.append(ptime)
        objDic.append(getStrvalue(strl,"宗地编号"))
        objDic.append(getStrvalue(strl,"宗地面积"))
        objDic.append(getStrvalue(strl,"宗地坐落"))
        objDic.append(getStrvalue(strl,"出让年限"))
        objDic.append(getStrvalue(strl,"容积率："))
        objDic.append(getStrvalue(strl,"建筑密度(%)"))
        objDic.append(getStrvalue(strl,"绿化率(%)"))
        objDic.append(getStrvalue(strl,"建筑限高(米)"))
        objDic.append(getStrvalue(strl,"土地用途"))
        objDic.append(getStrvalue(strl,"投资强度"))
        objDic.append(getStrvalue(strl,"保证金："))
        objDic.append(getStrvalue(strl,"估价报告备案号：","<td>"))
        objDic.append(getStrvalue(strl,"起始价："))
        objDic.append(getStrvalue(strl,"加价幅度"))
        objDic.append(getStrvalue(strl,"挂牌开始时间"))
        objDic.append(getStrvalue(strl,"挂牌截止时间"))
        objDic.append(getStrvalue(strl,"备注："))
        allDic.append(objDic)
        print "==============================="
        print "  "
        print "公布时间:"+ptime
        print "宗地编号"+":"+objDic[1]
        print "宗地面积"+":"+objDic[2]
        print "宗地坐落"+":"+objDic[3]
        print "出让年限"+":"+objDic[4]
        print "容积率"+":"+objDic[5]
        print "建筑密度"+":"+objDic[6]
        print "绿化率"+":"+objDic[7]
        print "建筑限高"+":"+objDic[8]
        print "土地用途"+":"+objDic[9]
        print "投资强度"+":"+objDic[10]
        print "保证金"+":"+objDic[11]
        print "估价报告备案号"+":"+objDic[12]
        print "起始价"+":"+objDic[13]
        print "加价幅度"+":"+objDic[14]
        print "挂牌开始时间"+":"+objDic[15]
        print "挂牌截止时间"+":"+objDic[16]
        print "备注"+":"+objDic[17]

#读取xls
''' 读取xls
#=================保持到xlsx
fname = "demo.xls"
print "打开文档，写入数据"
bk = xlrd.open_workbook(fname)

shxrange = range(bk.nsheets)
sheetname = "sheet1"

try:
    sh = bk.sheet_by_name(sheetname)
except:
    print "no sheet in %s named Sheet1" % fname
print "获取数据"

#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)
#===========================================================
'''
f= xlwt.Workbook()#创建工作簿
sheetname = "出让公告"
sheet = f.add_sheet(unicode(sheetname,'utf-8'),cell_overwrite_ok=True)
#创建表头
sheet.write(0,0,u'公布时间')
sheet.write(0,1,u'宗地编号')
sheet.write(0,2,u"宗地面积")
sheet.write(0,3,u"宗地坐落")
sheet.write(0,4,u"出让年限")
sheet.write(0,5,u"容积率%")
sheet.write(0,6,u"建筑密度%")
sheet.write(0,7,u"绿化率%")
sheet.write(0,8,u"建筑限高")
sheet.write(0,9,u"土地用途")
sheet.write(0,10,u"投资强度")
sheet.write(0,11,u"保证金")
sheet.write(0,12,u"估价报告备案号")
sheet.write(0,13,u"起始价")
sheet.write(0,14,u"加价幅度")
sheet.write(0,15,u"挂牌开始时间")
sheet.write(0,16,u"挂牌截止时间")
sheet.write(0,17,u"备注")

#写入
'''
print "================="
i=1
while i<nrows:
    rows = sh.row_values(i)
    sheet.write(i,0,rows[0])
    sheet.write(i,1,rows[1])
    sheet.write(i,2,rows[2])
    sheet.write(i,3,rows[3])
    sheet.write(i,4,rows[4])
    sheet.write(i,5,rows[5])
    sheet.write(i,6,rows[6])
    sheet.write(i,7,rows[7])
    i+=1

oldDic=[]
i=1
while i<nrows:
    rdic = []
    rows = sh.row_values(i)
    rdic.append(rows[0])
    rdic.append(rows[1])
    rdic.append(rows[2])
    rdic.append(rows[3])
    rdic.append(rows[4])
    rdic.append(rows[5])
    rdic.append(rows[6])
    rdic.append(rows[7])
    oldDic.append(rdic)
    i+=1

ishave = 0
i=0

while i<len(oldDic):
    rdics = oldDic[i]
    i+=1
    bm = rdics[0]
    if bm == dikuaibianma:
        ishave = 1
        i=100000
        print "==============已经拥有了"
'''

nrows = len(allDic)
i=0
while i<nrows:
    wdic = allDic[i]
    k=i+1
    print "新的数据，开始写入档案"
    wcount = len(wdic)
    wi = 0
    while wi<wcount:
        sheet.write(k,wi,unicode(wdic[wi],'utf-8'))
        wi+=1
    i+=1

f.save( u'顺德土地信息（出让公告）.xls')





